package com.liuxl.db.tool;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import com.github.pagehelper.Page;
import com.google.common.collect.Lists;
import com.liuxl.db.JdbcUtils;

import cn.hutool.core.collection.CollectionUtil;

public class MySQLDatabaseTool extends AbstractDatabaseTool {


	@Override
	public List<String> getTableNames(DataSource dataSource) throws SQLException {
		List<String> tableNames = Lists.newArrayList();
		String databaseName = getDatabaseName(dataSource);
		String sql = "select table_name from information_schema.TABLES where TABLE_SCHEMA = ?";
		List<Map<String, Object>> datas = JdbcUtils.executeQuery(dataSource, sql, databaseName);
		for (Map<String, Object> data : datas) {
			String tableName = data.get("table_name") == null ? "" : data.get("table_name").toString();
			tableNames.add(tableName);
		}
		return tableNames;
	}

	
	
	public String getDatabaseName(DataSource dataSource) throws SQLException {
		String databaseName = "";
		String sql = "select database() as databaseName";
		List<Map<String, Object>> results = JdbcUtils.executeQuery(dataSource, sql);
		if(CollectionUtil.isNotEmpty(results)) {
			databaseName = results.get(0).get("databaseName").toString() ;
		}
		return databaseName;
	}

	@Override
	public List<String> getColumnNames(DataSource dataSource, String tableName) throws SQLException {
		List<String> columnNames = Lists.newArrayList();
		String databaseName = getDatabaseName(dataSource);
		String sql = "select column_name from information_schema.columns where TABLE_SCHEMA = ? and table_name = ?";
		List<Map<String, Object>> datas = JdbcUtils.executeQuery(dataSource, sql, databaseName,tableName);
		for (Map<String, Object> data : datas) {
			String columnName = data.get("column_name") == null ? "" : data.get("column_name").toString();
			columnNames.add(columnName);
		}
		return columnNames;
	}
	
	@Override
	public String getPageSql(String sql) {
		StringBuilder sqlBuilder = new StringBuilder(sql.length() + 14);
	    sqlBuilder.append(sql);
	    sqlBuilder.append(" LIMIT ?, ? ");
		return sqlBuilder.toString();
	}

	@Override
	public void addPageParameter(Page<Map<String,Object>> page, List<Object> parameters) {
		parameters.add(page.getStartRow());
		parameters.add(page.getPageSize());
	}

	@Override
	public boolean existsTableName(DataSource dataSource, String tableName) throws SQLException {
		String databaseName = getDatabaseName(dataSource);
		String sql = "SELECT COUNT(1) FROM information_schema.TABLES WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?";
		List<Object[]> datas = JdbcUtils.executeQuery2(dataSource, sql, databaseName,tableName);
		Object obj = datas.get(0)[0];
		Integer count = obj == null ? 0 : Integer.valueOf(obj.toString());
		return count != 0;
	}

	@Override
	public boolean existsColumnName(DataSource dataSource, String tableName, String columnName) throws SQLException {
		String databaseName = getDatabaseName(dataSource);
		String sql = "SELECT COUNT(1) FROM information_schema.columns WHERE TABLE_SCHEMA = ? and TABLE_NAME = ? and COLUMN_NAME = ?";
		List<Object[]> datas = JdbcUtils.executeQuery2(dataSource, sql, databaseName,tableName,columnName);
		Object obj = datas.get(0)[0];
		Integer count = obj == null ? 0 : Integer.valueOf(obj.toString());
		return count != 0;
	}
}
